package com.chenjinyan.back.dao;

import com.chenjinyan.back.entity.BackUserEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface LoginCountRepository extends JpaRepository<BackUserEntity, Long> {

    /**
     * 返回今天登陆用户数量
     *
     * @return
     */
    @Query(value = "SELECT COUNT(1) FROM lost_and_find_login_count WHERE DATE_FORMAT( LOGIN_TIME,'%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')", nativeQuery = true)
    int getLoginCountToday();

    /**
     * 返回本月登陆用户数量
     *
     * @return
     */
    @Query(value = "SELECT COUNT(1) FROM  lost_and_find_login_count WHERE DATE_FORMAT( LOGIN_TIME, '%Y%m' ) = DATE_FORMAT( CURDATE( ) ,'%Y%m' ) ", nativeQuery = true)
    int getLoginCountMonth();

    /**
     * 返回今天上传失物登陆用户数量
     *
     * @return
     */
    @Query(value = "SELECT COUNT(1) FROM lost_and_find_goods WHERE DATE_FORMAT( CREATE_TIME,'%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');", nativeQuery = true)
    int getActiveCountToday();

    /**
     * 返回本月上传失物登陆用户数量
     *
     * @return
     */
    @Query(value = "SELECT COUNT(1) FROM lost_and_find_goods WHERE DATE_FORMAT( CREATE_TIME, '%Y%m' ) = DATE_FORMAT( CURDATE( ) ,'%Y%m' ) ", nativeQuery = true)
    int getActiveCountMonth();

    /**
     * 返回今天留言用户数量
     *
     * @return
     */
    @Query(value = "SELECT COUNT(1) FROM lost_and_find_message WHERE DATE_FORMAT( LEAVE_TIME,'%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');", nativeQuery = true)
    int getActiveMessageCountToday();

    /**
     * 返回本月留言用户数量
     *
     * @return
     */
    @Query(value = "SELECT COUNT(1) FROM  lost_and_find_message WHERE DATE_FORMAT( LEAVE_TIME, '%Y%m' ) = DATE_FORMAT( CURDATE( ) ,'%Y%m' ) ", nativeQuery = true)
    int getActiveMessageCountMonth();


    /**
     * 返回本天登陆用户数量
     *
     * @return
     */
    @Query(value = "SELECT HOUR(e.LOGIN_TIME) AS HOUR, COUNT(*) AS COUNT\n" +
            "FROM lost_and_find_login_count e\n" +
            "WHERE e.LOGIN_TIME LIKE concat(?1,'%')\n" +
            "GROUP BY HOUR(e.LOGIN_TIME)\n" +
            "ORDER BY HOUR(e.LOGIN_TIME)", nativeQuery = true)
    List<Object[]> getLoginCountByHour(String date);

    /**
     * 返回本月登陆用户数量
     *
     * @return
     */
    @Query(value = "SELECT DAY(e.LOGIN_TIME) AS DAY, COUNT(*) AS COUNT\n" +
            "FROM lost_and_find_login_count e\n" +
            "WHERE e.LOGIN_TIME LIKE concat(?1,'%')\n" +
            "GROUP BY DAY(e.LOGIN_TIME)\n" +
            "ORDER BY DAY(e.LOGIN_TIME)", nativeQuery = true)
    List<Object[]> getLoginCountByDay(String date);


    /**
     * 返回本天活跃用户数量
     *
     * @return
     */
    @Query(value = "SELECT HOUR(e.CREATE_TIME) AS HOUR, COUNT(*) AS COUNT\n" +
            "FROM lost_and_find_goods e\n" +
            "WHERE e.CREATE_TIME LIKE concat(?1,'%')\n" +
            "GROUP BY HOUR(e.CREATE_TIME)\n" +
            "ORDER BY HOUR(e.CREATE_TIME)", nativeQuery = true)
    List<Object[]> getActiveByHour(String date);

    /**
     * 返回本月活跃用户数量
     *
     * @return
     */
    @Query(value = "SELECT DAY(e.CREATE_TIME) AS DAY, COUNT(*) AS COUNT\n" +
            "FROM lost_and_find_goods e\n" +
            "WHERE e.CREATE_TIME LIKE concat(?1,'%')\n" +
            "GROUP BY DAY(e.CREATE_TIME)\n" +
            "ORDER BY DAY(e.CREATE_TIME)", nativeQuery = true)
    List<Object[]> getActiveByDay(String date);

}
